import sqlite3
import logging


# 数据库操作类
class dbTools(object):
    '''
    1.初始化数据库
    2.创建数据表
    3.执行增、删、改
    4.批量插入数据
    5.关闭数据库
    6.
    '''
    dbFile = 'personData.db'
    conn = None
    cur = None
    logger = None

    def __init__(self, falg=True):
        self.logger = logging.getLogger("applog")
        '''
        创建数据库，如果已经存在，建立数据库连接和数据游标
        '''
        self.conn = self.getConn()
        if falg:
            self.conn.row_factory = self._dict_factory
        self.cur = self.conn.cursor()
        try:
            res = self.cur.execute("select * from t_checkdata")
        except Exception as ex:
            self.logger.exception(ex)
            self._initTabel()

    @staticmethod
    def _dict_factory(cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    # 获取数据库连接
    def getConn(self):
        if self.conn is None:
            conn = sqlite3.connect(self.dbFile)
            if conn is None:
                self.logger.error("dbfile is not found")
            else:
                self.logger.debug("db connect is ok")
        else:
            conn = self.conn

        return conn

    # 关闭数据库
    def close(self):
        if self.conn is not None:
            self.cur.close()
            self.conn.close()
            self.logger.debug("db is close!")

    # 初始化数据表
    def _initTabel(self):
        checkDateTable = '''
               CREATE TABLE IF NOT EXISTS t_checkdata
               (id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                checkNo INTEGER,
                checkTime DATE ,
                checkDate DATE 
                )
        '''

        restDateTable = '''
            CREATE TABLE IF NOT EXISTS t_restdata
               (id INTEGER PRIMARY KEY AUTOINCREMENT,
                applyperson TEXT,
                leavetype TEXT,
                started DATE,
                finished DATE,
                leavetime float ,
                leavereason TEXT
                )
        '''

        setUpTable = '''
            CREATE TABLE IF NOT EXISTS t_setup
               (id INTEGER PRIMARY KEY AUTOINCREMENT,
                dtype INTEGER,
                content TEXT
                )
        '''

        resultsTable = '''
            CREATE TABLE IF NOT EXISTS t_results 
              (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
              name TEXT,
              checkNo INTEGER,
              checkDate DATE,
              dayofweek INTEGER,
              started DATE,
              finished DATE,
              laterTimes INTEGER,
              leavetime float,
              leavetype TEXT,	
              dtype  INTEGER,
              outTime float,
              subsidies INTEGER,
              remark TEXT	
            );
        '''
        tempTable = '''
                CREATE TABLE IF NOT EXISTS t_temp(
                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
                name TEXT, 
                checkNo INTEGER, 
                started DATE, 
                finished DATE, 
                checkDate DATE
                )
        '''
        self.cur.execute(checkDateTable)
        self.cur.execute(restDateTable)
        self.cur.execute(setUpTable)
        self.cur.execute(resultsTable)
        self.cur.execute(tempTable)
        self.conn.commit()

        self.logger.info("数据库初始化完成")

    # 批量插入数据
    def batchInsert(self, sql, values):
        '''
        批量插入数据
        :param tableName: 数据表名
        :param values: list 数据
        eg: INSERT INTO test values(?,?,?)
        :return:
        '''
        self.cur.executemany(sql, values)
        self.conn.commit()

    # 批量插入数据
    def batchUpdate(self, sql, values):
        '''
        批量插入数据
        :param tableName: 数据表名
        :param values: list 数据
        eg: INSERT INTO test values(?,?,?)
        :return:
        '''
        self.cur.executemany(sql, values)
        self.conn.commit()

    # 清空表
    def clear(self, tableName):
        '''
        清空表数据
        :param tableName: 表名
        :return:
        '''
        self.cur.execute(f"DELETE FROM {tableName}")
        self.conn.commit()

    def execute(self, sql, args=None, disc=True) -> list:
        '''
        执行数据库操作的通用方法
        :param disc: 返回为字典
        :param sql:
        :param args:
        :return:
        '''

        if args is None:
            self.cur.execute(sql)
        else:
            self.cur.execute(sql, args)
        logging.debug(sql)
        self.conn.commit()
        result = self.cur.fetchall()
        return result
